﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace _5___NorthwindCategories
{
    class Program
    {
        static void Main(string[] args)
        {
            SelectCategories();

            InsertProduct("osciloscopcinum", 3, false);

            SelectCategories();

        }

        private static void InsertProduct(string productName, int categoryID, bool discontinued)
        {
            SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=Northwind;Integrated Security=true");
            con.Open();
            using (con)
            {
                SqlCommand insertCommand = new SqlCommand("INSERT INTO Products (ProductName, CategoryID, Discontinued)" +
                    "VALUES (@name, @category, @disc)", con);
                insertCommand.Parameters.Add(new SqlParameter("@name", productName));
                insertCommand.Parameters.Add(new SqlParameter("@category", categoryID));
                insertCommand.Parameters.Add(new SqlParameter("@disc", discontinued));
                insertCommand.ExecuteNonQuery();
            }
        }

        private static void SelectCategories()
        {
            SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=Northwind;Integrated Security=true");
            con.Open();
            using (con)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT c.CategoryName, p.ProductName FROM Products p JOIN Categories c ON p.CategoryID=c.CategoryID order by c.CategoryName", con);
                SqlDataReader reader = command.ExecuteReader();
                using (reader)
                {
                    string prevCategory = "";
                    while (reader.Read())
                    {
                        string category = (string)reader["Categoryname"];
                        string product = (string)reader["ProductName"];
                        if (prevCategory == "" || category != prevCategory)
                        {
                            Console.WriteLine();
                            Console.WriteLine();
                            Console.Write("{0} - {1}", category, product);
                            prevCategory = category;
                        }
                        else
                        {
                            Console.Write(", {0}", product);
                        }
                    }
                }

            }
        }
    }
}
